# 基础管理模块功能接口
from flask import Blueprint, request, jsonify
from werkzeug.security import generate_password_hash, check_password_hash
from flask_jwt_extended import (create_access_token, jwt_required, get_jwt_identity, get_jwt, create_refresh_token,
                                decode_token)
import exts
import datetime
from gmssl import sm3, func
from pypinyin import pinyin, Style

basic_blueprint = Blueprint('basic', __name__)


# Token 刷新接口
@basic_blueprint.route('/refresh', methods=['POST'])
@jwt_required(refresh=True)
def refresh():
    """TOKEN 刷新接口"""
    current_user = get_jwt_identity()
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT user_id, status, school_id FROM Users WHERE user_id=?', (current_user,))
        info = cursor.fetchone()
        status = info['status']
        school_id = info['school_id']
        if status == 'inactive':
            # 将refresh token加入黑名单
            refresh_jti = get_jwt()['jti']
            cursor.execute('INSERT INTO TokenBlackList (user_id, jti, token_type, reason) VALUES (?, ?, ?, ?)',
                           (current_user, refresh_jti, 'refresh', 'User inactive'))
            conn.commit()
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户已被禁用'
            }), 400

        sql = """
        SELECT Roles.role_name FROM UserRoles
        JOIN Roles ON UserRoles.role_id = Roles.role_id
        WHERE UserRoles.user_id=? AND Roles.status='active'
        """
        cursor.execute(sql, (current_user,))
        result = cursor.fetchall()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在'
            }), 400
        role_names = [role['role_name'] for role in result]
        # 生成新的访问 Token
        new_access_token = create_access_token(identity=current_user, additional_claims={'roles': role_names,
                                                                                         'school_id': school_id})
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '刷新 Token 成功',
            'data': {
                'access_token': new_access_token
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理刷新 Token 请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 用户登录接口
@basic_blueprint.route('/login', methods=['POST'])
def login():
    """用户登录接口"""
    phone = request.json.get('phone')
    password = request.json.get('password')
    if not phone or not password:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '手机号或密码不能为空'
        }), 400
    password = exts.decrypt_aes(password)
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 使用参数化查询防止SQL注入
        sql = 'SELECT user_id, username, password_hash, status, school_id FROM Users WHERE phone=?'
        cursor.execute(sql, (phone,))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在或密码错误'
            }), 400
        user_id = result['user_id']
        password_hash = result['password_hash']
        status = result['status']
        school_id = result['school_id']
        if status == 'inactive':
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户已被禁用'
            }), 400
        if not check_password_hash(password_hash, password):
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在或密码错误'
            }), 400

        # 查询与用户绑定的角色名称
        sql1 = """
        SELECT Roles.role_name FROM UserRoles
        JOIN Roles ON UserRoles.role_id = Roles.role_id
        WHERE UserRoles.user_id=? AND Roles.status='active'
        """
        cursor.execute(sql1, (user_id,))
        roles = cursor.fetchall()
        role_names = [role['role_name'] for role in roles]
        access_token = create_access_token(identity=user_id, additional_claims={'roles': role_names,
                                                                                'school_id': school_id})
        refresh_token = create_refresh_token(identity=user_id)

        # 查询学校名称
        sql2 = 'SELECT school_name FROM School WHERE school_id=?'
        cursor.execute(sql2, (school_id,))
        result2 = cursor.fetchone()
        school_name = result2['school_name']

        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '登录成功',
            'data': {
                'user_id': result['user_id'],
                'username': result['username'],
                'school_id': school_id,
                'school_name': school_name,
                'roles': role_names,
                'access_token': access_token,
                'refresh_token': refresh_token
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理登录请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 用户退出登录接口
@basic_blueprint.route('/logout', methods=['POST'])
@jwt_required()  # 该装饰器要求访问令牌
def logout():
    """用户退出登录接口"""
    user_id = get_jwt_identity()  # 假设JWT中存储了用户ID
    access_jti = get_jwt()['jti']  # 获取访问令牌的JTI
    refresh_token = request.json.get('refresh_token')  # 直接获取刷新令牌字符串

    if refresh_token:
        try:
            # 解码刷新令牌以获取JTI
            decoded_refresh = decode_token(refresh_token)
            refresh_jti = decoded_refresh['jti']
        except Exception as e:
            return jsonify({
                'code': 500,
                'status': 'error',
                'message': f"刷新令牌解码失败，{e}"
            }), 500
    else:
        refresh_jti = None

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 插入访问令牌到黑名单
        cursor.execute('INSERT INTO TokenBlackList (user_id, jti, token_type, reason) VALUES (?, ?, ?, ?)',
                       (user_id, access_jti, 'access', 'Logged out'))
        # 如果存在，插入刷新令牌到黑名单
        if refresh_jti:
            cursor.execute('INSERT INTO TokenBlackList (user_id, jti, token_type, reason) VALUES (?, ?, ?, ?)',
                           (user_id, refresh_jti, 'refresh', 'Logged out'))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '成功退出登录'
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': f"无法处理退出登录请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 用户注册接口
@basic_blueprint.route('/register', methods=['POST'])
def register():
    """用户注册接口"""
    username = request.json.get('username')
    phone = request.json.get('phone')
    password = request.json.get('password')
    question = request.json.get('question')
    answer = request.json.get('answer')
    school_id = request.json.get('school_id')
    if not username or not phone or not password or not question or not answer:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 检查手机号是否已经注册
        sql = 'SELECT user_id FROM Users WHERE phone=?'
        cursor.execute(sql, (phone,))
        result = cursor.fetchone()
        if result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '手机号已经注册'
            }), 400

        password = exts.decrypt_aes(password)
        answer = exts.decrypt_aes(answer)
        # 生成密码哈希
        password_hash = generate_password_hash(password)
        answer_sm3 = sm3.sm3_hash(func.bytes_to_list(answer.encode('utf-8')))
        # 插入用户信息
        sql1 = ('INSERT INTO Users (username, phone, password_hash, security_question, security_answer, school_id) '
                'VALUES (?, ?, ?, ?, ?, ?)')
        cursor.execute(sql1, (username, phone, password_hash, question, answer_sm3, school_id))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '注册成功',
            'data': {
                'user_id': cursor.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理注册请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 获取密保问题接口
@basic_blueprint.route('/security_question', methods=['GET'])
def get_security_question():
    """获取密保问题接口"""
    phone = request.args.get('phone')
    if not phone:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '手机号不能为空'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        sql = 'SELECT security_question FROM Users WHERE phone=?'
        cursor.execute(sql, (phone,))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在'
            }), 400
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '获取密保问题成功',
            'data': {
                'question': result['security_question']
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理获取密保问题请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 重置密码接口
@basic_blueprint.route('/reset-password', methods=['PATCH'])
def reset_password():
    """重置密码接口"""
    phone = request.json.get('phone')
    password = request.json.get('password')
    answer = request.json.get('answer')
    if not phone or not password or not answer:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    password = exts.decrypt_aes(password)
    answer = exts.decrypt_aes(answer)
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询用户ID
        sql = 'SELECT user_id, security_answer FROM Users WHERE phone=?'
        cursor.execute(sql, (phone,))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在'
            }), 400
        user_id, security_answer = result
        answer = sm3.sm3_hash(func.bytes_to_list(answer.encode('utf-8')))
        if answer != security_answer:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '密保问题回答错误'
            }), 400

        # 更新密码
        password_hash = generate_password_hash(password)
        sql1 = 'UPDATE Users SET password_hash=? WHERE user_id=?'
        cursor.execute(sql1, (password_hash, user_id))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '重置密码成功'
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理重置密码请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 用户信息添加接口
@basic_blueprint.route('/users/add', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def add_user():
    """用户信息添加接口"""
    username = request.json.get('username', None)
    school_id = request.json.get('school_id', None)
    phone = request.json.get('phone', None)
    password = request.json.get('password', None)
    email = request.json.get('email', None)
    question = request.json.get('question', None)
    answer = request.json.get('answer', None)
    status = request.json.get('status', None)

    if not username and not phone:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '用户名和手机号不可为空'
        }), 400

    if not password:
        password = "Hxfw" + str(phone)[-4:]
    else:
        password = exts.decrypt_aes(password)
    operator_id = get_jwt_identity()
    password_hash = generate_password_hash(password)
    if not answer:
        pinyin_name = pinyin(username, style=Style.NORMAL)
        pinyin_str = ''.join([i[0] for i in pinyin_name])
        answer = pinyin_str + str(phone)[-4:]
    else:
        answer = exts.decrypt_aes(answer)
    answer_sm3 = sm3.sm3_hash(func.bytes_to_list(answer.encode('utf-8')))
    if not question:
        question = "初始始密保问题答案是什么？"

    conn = None
    try:

        conn = exts.get_db()
        cursor = conn.cursor()
        # 检查手机号是否已经注册
        sql = 'SELECT user_id FROM Users WHERE phone=?'
        cursor.execute(sql, (phone,))
        result = cursor.fetchone()
        if result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '手机号已经注册'
            }), 400

        # 插入用户信息
        sql1 = ('INSERT INTO Users (username, phone, password_hash, email, security_question, security_answer, '
                'status, create_staff, operate_staff, school_id) '
                'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
        cursor.execute(sql1, (username, phone, password_hash, email, question, answer_sm3, status,
                              operator_id, operator_id, school_id))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '添加用户成功',
            'data': {
                'user_id': cursor.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理添加用户请求，{}".format(e)
        }), 500


# 用户信息查询接口
@basic_blueprint.route('/users/query', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_user():
    """用户信息查询接口"""
    user_id = request.args.get('user_id', None)
    school_id = request.args.get('school_id', None)
    username = request.args.get('username', None)
    phone = request.args.get('phone', None)
    email = request.args.get('email', None)
    status = request.args.get('status', None)
    page = request.args.get('page', default=1, type=int)
    page_size = request.args.get('page_size', default=10, type=int)

    if not page or not page_size:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '必需请求参数不完整'
        }), 400

    if school_id is None:
        school_id = get_jwt()['school_id']

    info = {
        'user_id': user_id,
        'school_id': school_id,
        'username': username,
        'phone': phone,
        'email': email,
        'status': status
    }
    query_condition = ' AND '.join([f"{key}='{value}'" for key, value in info.items() if value])
    if query_condition:
        query_condition = 'WHERE ' + query_condition

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(f"SELECT COUNT(DISTINCT user_id) FROM Users {query_condition}")
        total_records = cursor.fetchone()[0]
        total_pages = total_records // page_size + 1 if total_records % page_size != 0 else total_records // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 400
        # 按照user_id降序排列
        sql = f"""
        SELECT DISTINCT user_id, username, phone, email, status, create_time, create_staff, operate_time, operate_staff, school_id
        FROM Users {query_condition}
        ORDER BY user_id DESC
        LIMIT {page_size} OFFSET {(page - 1) * page_size}
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        result = [dict(row) for row in result]
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '未查询到用户信息'
            }), 400

        # 通过关联查询获取创建者和操作者的用户名
        for user in result:
            create_staff = user['create_staff']
            operate_staff = user['operate_staff']
            sql1 = 'SELECT username FROM Users WHERE user_id=?'
            cursor.execute(sql1, (create_staff,))
            result1 = cursor.fetchone()
            if result1:
                user['create_staff'] = result1['username']
            else:
                user['create_staff'] = "无"
            cursor.execute(sql1, (operate_staff,))
            result2 = cursor.fetchone()
            if result2:
                user['operate_staff'] = result2['username']
            else:
                user['operate_staff'] = "无"

        # 通过关联查询获取用户关联的角色名称
        for user in result:
            user_id = user['user_id']
            sql1 = """
            SELECT UserRoles.id, Roles.role_name FROM UserRoles
            JOIN Roles ON UserRoles.role_id = Roles.role_id
            WHERE UserRoles.user_id=?
            """
            cursor.execute(sql1, (user_id,))
            roles = cursor.fetchall()
            # 为用户添加角色信息包括角色ID和角色名称
            user['roles'] = [dict(role) for role in roles]

        # 通过查询获取学校名称
        for user in result:
            school_id = user['school_id']
            sql1 = 'SELECT school_name FROM School WHERE school_id=?'
            cursor.execute(sql1, (school_id,))
            result1 = cursor.fetchone()
            if result1:
                user['school_name'] = result1['school_name']
            else:
                user['school_name'] = "未知"

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(result),
                "records": result
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理查询用户请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 用户信息更新接口
@basic_blueprint.route('/users/<int:user_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_user(user_id):
    """用户信息更新接口"""
    valid_params = ['username', 'phone', 'password', 'email', 'question', 'answer', 'status', 'school_id']
    required_params = ['username', 'phone', 'password']
    data = {key: request.json.get(key) for key in valid_params}

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)

    if 'password' in data.keys():
        data['password'] = exts.decrypt_aes(data['password'])
    if 'answer' in data.keys():
        data['answer'] = exts.decrypt_aes(data['answer'])

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400

    if 'password' in data.keys():
        data['password_hash'] = generate_password_hash(data['password'])
        data.pop('password')
    if 'answer' in data.keys():
        data['security_answer'] = sm3.sm3_hash(func.bytes_to_list(data['answer'].encode('utf-8')))
        data.pop('answer')
    if 'question' in data.keys():
        data['security_question'] = data['question']
        data.pop('question')

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        current_user = get_jwt_identity()
        data['operate_time'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        data['operate_staff'] = current_user
        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE Users SET {set_clause} WHERE user_id = ?'
        params = tuple(data.values()) + (user_id,)
        cur.execute(sql, params)
        if cur.rowcount == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "更新失败",
                "details": f"没有找到 user_id 为 {user_id} 的记录。"
            }), 404
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "更新成功",
            "data": {
                "user_id": user_id
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新用户信息请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 用户信息删除接口
@basic_blueprint.route('/users/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('basic', '*')
def delete_user():
    """用户信息删除接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for user_id in data:
            sql = "DELETE FROM Users WHERE user_id=?"
            cur.execute(sql, (user_id,))
            if cur.rowcount == 0:
                result.append({
                    'user_id': user_id,
                    'status': 'error',
                    'message': '用户不存在'
                })
            else:
                result.append({
                    'user_id': user_id,
                    'status': 'success',
                    'message': '用户删除成功'
                })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理删除用户信息请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 用户状态更新接口
@basic_blueprint.route('/users/status', methods=['PATCH'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_user_status():
    """用户状态更新接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        current_user = get_jwt_identity()
        result = []
        for user_id in data:
            sql = "SELECT status FROM Users WHERE user_id=?"
            cur.execute(sql, (user_id,))
            status = cur.fetchone()

            if not status:
                result.append({
                    'user_id': user_id,
                    'status': 'error',
                    'message': '用户不存在'
                })
                continue
            new_status = 'active' if status["status"] == 'inactive' else 'inactive'
            sql1 = "UPDATE Users SET status=?, operate_time=?, operate_staff=? WHERE user_id=?"
            cur.execute(sql1, (new_status, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                               current_user, user_id))
            result.append({
                'user_id': user_id,
                'status': 'success',
                'message': '用户状态更新成功',
                'detail': '用户状态由 {} 更新为 {}'.format(status["status"], new_status)
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新用户状态请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 用户密码重置接口
@basic_blueprint.route('/users/reset', methods=['PATCH'])
@jwt_required()
@exts.permission_required('basic', '*')
def reset_user_password():
    """用户密码重置接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        current_user = get_jwt_identity()
        for user_id in data:
            sql = "SELECT phone FROM Users WHERE user_id=?"
            cur.execute(sql, (user_id,))
            phone = cur.fetchone()
            if not phone:
                result.append({
                    'user_id': user_id,
                    'status': 'error',
                    'message': '用户不存在'
                })
                continue
            new_password = "Hxfw" + str(phone["phone"])[-4:]
            new_password_hash = generate_password_hash(new_password)
            sql1 = "UPDATE Users SET password_hash=?, operate_time=?, operate_staff=? WHERE user_id=?"
            cur.execute(sql1, (new_password_hash, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                               current_user, user_id))
            result.append({
                'user_id': user_id,
                'status': 'success',
                'message': '用户密码重置成功',
                'detail': '新密码为 {}'.format(new_password)
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理用户密码重置请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 用户角色绑定接口
@basic_blueprint.route('/users/bind', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def bind_user_role():
    """用户角色绑定接口"""
    role_id = request.json.get('role_id')
    user_id = request.json.get('user_id')
    if not role_id or not user_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询用户是否存在
        sql = 'SELECT user_id FROM Users WHERE user_id=?'
        cursor.execute(sql, (user_id,))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在'
            }), 400

        # 查询角色是否存在
        sql1 = 'SELECT role_id FROM Roles WHERE role_id=?'
        cursor.execute(sql1, (role_id,))
        result1 = cursor.fetchone()
        if not result1:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '角色不存在'
            }), 400

        # 查询用户是否已经绑定了该角色
        sql2 = 'SELECT * FROM UserRoles WHERE user_id=? AND role_id=?'
        cursor.execute(sql2, (user_id, role_id))
        result2 = cursor.fetchone()
        if result2:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户已经绑定了该角色'
            }), 400

        # 绑定用户角色
        sql3 = 'INSERT INTO UserRoles (user_id, role_id) VALUES (?, ?)'
        cursor.execute(sql3, (user_id, role_id))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '绑定用户角色成功',
            'data': {
                'id': cursor.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理绑定用户角色请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 用户角色解绑接口
@basic_blueprint.route('/users/unbind', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def unbind_user_role():
    """用户角色解绑接口"""
    user_id = request.json.get('user_id')
    role_id = request.json.get('role_id')
    if not role_id or not user_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询绑定关系是否存在
        sql = 'SELECT id FROM UserRoles WHERE user_id=? AND role_id=?'
        cursor.execute(sql, (user_id, role_id))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户未绑定该角色'
            }), 400
        # 解绑用户角色
        sql1 = 'DELETE FROM UserRoles WHERE id=?'
        cursor.execute(sql1, (result['id'],))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '解绑用户角色成功',
            'data': {
                'id': result['id']
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理解绑用户角色请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 获取用户角色关联信息接口
@basic_blueprint.route('/users/bind_info', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def get_user_role_info():
    """获取用户角色关联信息接口"""
    user_id = request.args.get('user_id', type=int)
    if not user_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询用户是否存在
        sql = 'SELECT user_id FROM Users WHERE user_id=?'
        cursor.execute(sql, (user_id,))
        result = cursor.fetchone()
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '用户不存在'
            }), 400
        # 查询用户角色关联信息
        sql1 = 'SELECT id, role_id FROM UserRoles WHERE user_id=?'
        cursor.execute(sql1, (user_id,))
        result1 = cursor.fetchall()
        result1 = [dict(row) for row in result1]
        # 查询所有状态为 active 的角色信息
        sql2 = "SELECT role_id, role_name FROM Roles WHERE status='active'"
        cursor.execute(sql2)
        result2 = cursor.fetchall()
        result2 = [dict(row) for row in result2]
        # 为角色信息添加是否已经绑定的标志
        for role in result2:
            role['bind'] = False
            for item in result1:
                if role['role_id'] == item['role_id']:
                    role['bind'] = True
                    break
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '获取用户角色关联信息成功',
            'data': result2
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理获取用户角色关联信息请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色信息查询接口
@basic_blueprint.route('/roles/query', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_role():
    """角色信息查询接口"""
    role_id = request.args.get('role_id')
    role_name = request.args.get('role_name')
    description = request.args.get('description')
    status = request.args.get('status')
    page = request.args.get('page', default=1, type=int)
    page_size = request.args.get('page_size', default=10, type=int)

    if not page or not page_size:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '必需请求参数不完整'
        }), 400
    info = {
        'role_id': role_id,
        'role_name': role_name,
        'status': status
    }
    query_condition = ' AND '.join([f"{key}='{value}'" for key, value in info.items() if value])
    if query_condition:
        query_condition = 'WHERE ' + query_condition
    # 支持 description 模糊查询
    if description and query_condition:
        query_condition += f" AND description LIKE '%{description}%'"
    elif description:
        query_condition = f"WHERE description LIKE '%{description}%'"
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(f"SELECT COUNT(DISTINCT role_id) FROM Roles {query_condition}")
        total_records = cursor.fetchone()[0]
        total_pages = total_records // page_size + 1 if total_records % page_size != 0 else total_records // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 400
        # 按照role_id降序排列
        sql = f"""
        SELECT DISTINCT role_id, role_name, description, status, create_time, update_time
        FROM Roles {query_condition}
        ORDER BY role_id DESC
        LIMIT {page_size} OFFSET {(page - 1) * page_size}
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        result = [dict(row) for row in result]
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '未查询到角色信息'
            }), 400

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(result),
                "records": result
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理查询角色请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色信息添加接口
@basic_blueprint.route('/roles/add', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def add_role():
    """角色信息添加接口"""
    role_name = request.json.get('role_name', None)
    description = request.json.get('description', None)
    status = request.json.get('status', 'active')
    if not role_name:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 检查角色名称是否已经存在
        sql = 'SELECT role_id FROM Roles WHERE role_name=?'
        cursor.execute(sql, (role_name,))
        result = cursor.fetchone()
        if result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '角色名称已经存在'
            }), 400

        # 插入角色信息
        sql1 = ('INSERT INTO Roles (role_name, description, status, update_time) '
                'VALUES (?, ?, ?, ?)')
        cursor.execute(sql1, (role_name, description, status, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '添加角色成功',
            'data': {
                'role_id': cursor.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理添加角色请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色信息更新接口
@basic_blueprint.route('/roles/<int:role_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_role(role_id):
    """角色信息更新接口"""
    valid_params = ['role_name', 'description', 'status']
    required_params = ['role_name', 'status']
    data = {key: request.json.get(key) for key in valid_params}

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 检查角色名称是否已经存在
        if 'role_name' in data.keys():
            sql = 'SELECT role_id FROM Roles WHERE role_name=? AND role_id!=?'
            cur.execute(sql, (data['role_name'], role_id))
            result = cur.fetchone()
            if result:
                return jsonify({
                    'code': 400,
                    'status': 'error',
                    'message': '角色名称已经存在'
                }), 400
        # 更新角色信息
        data['update_time'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE Roles SET {set_clause} WHERE role_id = ?'
        params = tuple(data.values()) + (role_id,)
        cur.execute(sql, params)
        if cur.rowcount == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "更新失败",
                "details": f"没有找到 role_id 为 {role_id} 的记录。"
            }), 404
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "更新成功",
            "data": {
                "role_id": role_id
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新角色信息请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 角色信息删除接口
@basic_blueprint.route('/roles/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('basic', '*')
def delete_role():
    """角色信息删除接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for role_id in data:
            sql = "DELETE FROM Roles WHERE role_id=?"
            cur.execute(sql, (role_id,))
            if cur.rowcount == 0:
                result.append({
                    'role_id': role_id,
                    'status': 'error',
                    'message': '角色不存在'
                })
            else:
                result.append({
                    'role_id': role_id,
                    'status': 'success',
                    'message': '角色删除成功'
                })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理删除角色信息请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 角色状态更新接口
@basic_blueprint.route('/roles/status', methods=['PATCH'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_role_status():
    """角色状态更新接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        current_user = get_jwt_identity()
        result = []
        for role_id in data:
            sql = "SELECT status FROM Roles WHERE role_id=?"
            cur.execute(sql, (role_id,))
            status = cur.fetchone()

            if not status:
                result.append({
                    'role_id': role_id,
                    'status': 'error',
                    'message': '角色不存在'
                })
                continue
            new_status = 'active' if status["status"] == 'inactive' else 'inactive'
            sql1 = "UPDATE Roles SET status=?, update_time=? WHERE role_id=?"
            cur.execute(sql1, (new_status, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                               role_id))
            result.append({
                'role_id': role_id,
                'status': 'success',
                'message': '角色状态更新成功',
                'detail': '角色状态由 {} 更新为 {}'.format(status["status"], new_status)
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新角色状态请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 角色菜单绑定接口
@basic_blueprint.route('/roles/bind', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def bind_role_menu():
    """角色菜单绑定/解绑接口"""
    role_id = request.json.get('role_id')
    menu_id = request.json.get('menu_id')
    if not role_id or not menu_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT id FROM RoleMenus WHERE role_id=? AND menu_id=?', (role_id, menu_id))
        result = cursor.fetchone()
        if result:
            cursor.execute('DELETE FROM RoleMenus WHERE id=?', (result['id'],))
            conn.commit()
            return jsonify({
                'code': 200,
                'status': 'success',
                'message': '解绑角色菜单成功',
                'data': {
                    'id': result['id']
                }
            }), 200
        else:
            cursor.execute('INSERT INTO RoleMenus (role_id, menu_id) VALUES (?, ?)', (role_id, menu_id))
            conn.commit()
            return jsonify({
                'code': 200,
                'status': 'success',
                'message': '绑定角色菜单成功',
                'data': {
                    'id': cursor.lastrowid
                }
            }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理绑定角色菜单请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色菜单绑定详情查询接口
@basic_blueprint.route('/roles-menus/query', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_role_menus():
    """角色绑定的菜单查询接口"""
    role_id = request.args.get('role_id', None, type=int)
    if not role_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.execute('SELECT menu_id FROM RoleMenus WHERE role_id=?', (role_id,))
        result = cur.fetchall()
        bind_result = [row['menu_id'] for row in result]
        cur.execute('SELECT * FROM Menus')
        all_menus = cur.fetchall()
        all_menus = [dict(row) for row in all_menus]
        for menu in all_menus:
            if menu['menu_id'] in bind_result:
                menu['bind_status'] = '已绑定'
            else:
                menu['bind_status'] = '未绑定'
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '查询成功',
            'data': all_menus
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理查询角色绑定的菜单请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色绑定的菜单查询接口
@basic_blueprint.route('/roles-menus/bind-query', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_role_bind_menus():
    """角色绑定的菜单查询接口"""
    role_id = request.args.get('role_id', None, type=int)
    if not role_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整'
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.execute('SELECT menu_id FROM RoleMenus WHERE role_id=?', (role_id,))
        result = cur.fetchall()
        menu_ids = [row['menu_id'] for row in result]
        cur.execute("SELECT * FROM Menus WHERE menu_id IN ({}) AND status='active'"
                    .format(','.join([str(i) for i in menu_ids])))
        result = cur.fetchall()
        result = [dict(row) for row in result]
        # 如果 parent_id 不为空，则将 parent_id 对应的菜单新增 children 字段,并将其插入到 parent_id 对应的菜单的 children 列表中，然后删除该菜单
        for item in result:
            if item['parent_id']:
                for item1 in result:
                    if item1['menu_id'] == item['parent_id']:
                        if 'children' not in item1:
                            item1['children'] = []
                        item1['children'].append(item)
                result.remove(item)
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '查询成功',
            'data': result
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理查询角色绑定的菜单请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色权限分配接口
@basic_blueprint.route('/roles/permission', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def assign_role_permission():
    """角色权限分配接口"""
    permissions = request.json.get('permissions', None)
    role_id = request.json.get('role_id', None)
    if not permissions or not role_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整, 请提供角色ID和权限信息'
        }), 400
    # 检查permissions的格式是否正确
    op = ['basic', 'students', 'info_changes', 'classes', 'exams', 'scores', 'knowledge_points', 'questions',
          'gongzhonghao']
    for key, value in permissions.items():
        if (key not in op) or (not isinstance(value, list)):
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '请求参数错误',
                'details': '权限参数错误, 请检查权限参数'
            }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        permissions_list = []
        for key, value in permissions.items():
            if value:
                permissions_list.append(key + '-' + '-'.join(value))
            else:
                permissions_list.append(key)
        permissions_str = '&'.join(permissions_list)
        sql = "UPDATE Roles SET permissions=?, update_time=? WHERE role_id=?"
        cur.execute(sql, (permissions_str, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), role_id))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '权限分配成功'
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "错误详情：{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 角色权限查询接口
@basic_blueprint.route('/roles/permission/query/<int:role_id>', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_role_permission(role_id):
    """角色权限查询接口"""
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.execute('SELECT permissions FROM Roles WHERE role_id=?', (role_id,))
        result = cur.fetchone()
        if not result:
            return jsonify({
                'code': 404,
                'status': 'not_found',
                'message': '未查询到角色权限信息'
            }), 404
        permissions = result['permissions']
        permissions_list = permissions.split('&')
        permissions_dict = {}
        for item in permissions_list:
            if '-' in item:
                key, value = item.split('-', 1)  # 仅拆分第一个 '-'
                if key not in permissions_dict:
                    permissions_dict[key] = []
                permissions_dict[key].extend(value.split('-'))
            else:
                if item not in permissions_dict:
                    permissions_dict[item] = []
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '查询成功',
            'data': {
                'permissions': permissions_dict
            }
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "错误详情：{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 菜单新增接口
@basic_blueprint.route('/menus/add', methods=['POST'])
@jwt_required()
@exts.permission_required('basic', '*')
def add_menu():
    """菜单新增接口"""
    parent_id = request.json.get('parent_id', None)
    menu_name = request.json.get('menu_name', None)
    menu_type = request.json.get('menu_type', None)
    path = request.json.get('path', None)
    icon = request.json.get('icon', None)
    sort = request.json.get('sort', 0)
    status = request.json.get('status', 'active')

    if not menu_name or not menu_type or not path:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '请求参数不完整',
            'details': '菜单名称 menu_name 、菜单类型 menu_type 和 路径 path 不能为空'
        }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        update_time = create_time
        sql = ('INSERT INTO Menus (parent_id, menu_name, menu_type, path, icon, sort, status, create_time, '
               'update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)')
        cursor.execute(sql, (parent_id, menu_name, menu_type, path, icon, sort, status, create_time, update_time))
        conn.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '添加菜单成功',
            'data': {
                'menu_id': cursor.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器错误',
            'detail': f'无法处理添加菜单请求，{e}'
        }), 500
    finally:
        if conn:
            conn.close()


# 菜单更新接口
@basic_blueprint.route('/menus/update/<int:menu_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_menu(menu_id):
    """菜单更新接口"""
    valid_params = ['parent_id', 'menu_name', 'menu_type', 'path', 'icon', 'sort', 'status']
    required_params = ['menu_name', 'menu_type', 'path', 'status']
    data = {key: request.json.get(key) for key in valid_params}

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        data['update_time'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE Menus SET {set_clause} WHERE menu_id = ?'
        params = tuple(data.values()) + (menu_id,)
        cursor.execute(sql, params)
        if cursor.rowcount == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "更新失败",
                "details": f"没有找到 menu_id 为 {menu_id} 的记录。"
            }), 404
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "更新成功",
            "data": {
                "menu_id": menu_id
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新菜单请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 菜单删除接口
@basic_blueprint.route('/menus/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('basic', '*')
def delete_menu():
    """菜单删除接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for menu_id in data:
            sql = "DELETE FROM Menus WHERE menu_id=?"
            cur.execute(sql, (menu_id,))
            if cur.rowcount == 0:
                result.append({
                    'menu_id': menu_id,
                    'status': 'error',
                    'message': '菜单不存在'
                })
            else:
                result.append({
                    'menu_id': menu_id,
                    'status': 'success',
                    'message': '菜单删除成功'
                })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理删除菜单请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 菜单状态更新接口
@basic_blueprint.route('/menus/status', methods=['PATCH'])
@jwt_required()
@exts.permission_required('basic', '*')
def update_menu_status():
    """菜单状态更新接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for menu_id in data:
            sql = "SELECT status FROM Menus WHERE menu_id=?"
            cur.execute(sql, (menu_id,))
            status = cur.fetchone()
            if not status:
                result.append({
                    'menu_id': menu_id,
                    'status': 'error',
                    'message': '菜单不存在'
                })
                continue
            new_status = 'active' if status["status"] == 'inactive' else 'inactive'
            sql1 = "UPDATE Menus SET status=?, update_time=? WHERE menu_id=?"
            cur.execute(sql1, (new_status, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                               menu_id))
            result.append({
                'menu_id': menu_id,
                'status': 'success',
                'message': '菜单状态更新成功',
                'detail': '菜单状态由 {} 更新为 {}'.format(status["status"], new_status)
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器错误",
            "detail": f"无法处理更新菜单状态请求，{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 菜单查询接口
@basic_blueprint.route('/menus/query', methods=['GET'])
@jwt_required()
@exts.permission_required('basic', '*')
def query_menu():
    """菜单查询接口"""
    menu_name = request.args.get('menu_name', type=str, default=None)
    menu_type = request.args.get('menu_type', type=str, default=None)
    path = request.args.get('path', type=str, default=None)
    status = request.args.get('status', type=str, default=None)

    where_clause = []
    if menu_name:
        where_clause.append(f"menu_name LIKE '%{menu_name}%'")
    if menu_type:
        where_clause.append(f"menu_type='{menu_type}'")
    if path:
        where_clause.append(f"path LIKE '%{path}%'")
    if status:
        where_clause.append(f"status='{status}'")

    where_clause = ' AND '.join(where_clause) if where_clause else '1=1'

    sql = f"""
    SELECT menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, create_time, update_time
    FROM Menus
    WHERE {where_clause}
    """
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        result = [dict(row) for row in result]
        if not result:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '未查询到菜单信息'
            }), 400
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '查询成功',
            'data': result
        }), 200
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': "服务器错误",
            'detail': "无法处理查询菜单请求，{}".format(e)
        }), 500
    finally:
        if conn:
            conn.close()
